/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.xsengine.db;

/**
 *
 * @author baga
 */
import com.xsengine.SplitNormalizeToken;
import com.xsengine.StemmerEng;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author baga
 */
public class DBOperator {

    private String userName;
    private String password;
    private String DBName;
    Connection conn;

    public DBOperator(String userName, String password, String DBName) {
        this.userName = userName;
        this.password = password;
        this.DBName = DBName;

        String connStr = "jdbc:mysql://localhost:3306/" +
                this.DBName + "?user=" + this.userName +
                "&password=" + this.password;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            try {
                conn = DriverManager.getConnection(connStr);
            } catch (SQLException se) {
                se.printStackTrace();
            }
        } catch (ClassNotFoundException ce) {
            ce.printStackTrace();
        }
    }

    public DBOperator() {
        this("root", "root", "xsedb");
    }

    public int countDoc() {
        int docSum = 0;
        try {
            PreparedStatement pstm = conn.prepareStatement("select count(*) " +
                    "from t_doc");
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                docSum = rs.getInt(1);
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
        return docSum;
    }

    public String getDoc(String context) {
        String temp = null;
        try {
            PreparedStatement pstm = conn.prepareStatement("select posting " +
                    "from dic_post where dictionary like 'upper(?)%'");
            pstm.setString(1, context.toUpperCase());
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                temp = rs.getString(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBOperator.class.getName()).log(Level.SEVERE, null, ex);
        }
        return temp;
    }

    public String getPosting(String context) {
        String temp = null;
        try {
            PreparedStatement pstm = conn.prepareStatement("select posting " +
                    "from dic_post where dictionary = ?");
            pstm.setString(1, context.toUpperCase());
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                temp = rs.getString(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBOperator.class.getName()).log(Level.SEVERE, null, ex);
        }
        return temp;
    }

    public String getPosting(String context, String table) {
        String temp = null;
        try {
            PreparedStatement pstm = conn.prepareStatement("select posting " +
                    "from "+ table +" where dictionary = ?");
            pstm.setString(1, context.toUpperCase());
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                temp = rs.getString(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBOperator.class.getName()).log(Level.SEVERE, null, ex);
        }
        return temp;
    }

//    public String[] stopWords(String table) {
//        int sum;
//        String[] arrWord = null;
//        try {
//            PreparedStatement ps = conn.prepareCall("select word from " +
//                    table + " order by count desc");
//            ResultSet rs = ps.executeQuery();
//            while(rs.next()) {
//
//            }
//        } catch (SQLException se) {
//        }
//
//        return arrWord;
//    }

    public String[] getAllDoc() {
        Vector newVector = new Vector();
        try {
            PreparedStatement pstm = conn.prepareCall("select dictionary " +
                    "from dic_post");
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                newVector.add(rs.getString("dictionary"));
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
        String[] tempString = new String[newVector.size()];
        return (String[]) newVector.toArray(tempString);
    }

    public String[] getAllDoc(String table) {
        Vector newVector = new Vector();
        try {
            PreparedStatement pstm = conn.prepareCall("select dictionary " +
                    "from " + table);
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                newVector.add(rs.getString("dictionary"));
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
        String[] tempString = new String[newVector.size()];
        return (String[]) newVector.toArray(tempString);
    }

    public String[] getAllDoc(String table, String query) {
        Vector newVector = new Vector();
        String[] strQuery = SplitNormalizeToken.getNormalTokens(query);
        StringBuilder sb = new StringBuilder();
        for (int i=0; i<strQuery.length; i++) {
            sb.append(" lower(dictionary) like '%#"+strQuery[i].toLowerCase()+"#%' ");
            if (i < strQuery.length - 1) {
                sb.append(" or ");
            }
        }

        try {
            PreparedStatement pstm = conn.prepareCall("select dictionary " +
                    "from " + table + " where " + sb.toString());
            System.out.println("select dictionary " +
                    "from " + table + " where " + sb.toString());
//            pstm.setString(1, sb.toString());
            ResultSet rs = pstm.executeQuery();
            
            while (rs.next()) {
                String abc = rs.getString("dictionary");
                newVector.add(abc);
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
        String[] tempString = new String[newVector.size()];
        return (String[]) newVector.toArray(tempString);
    }

    public ArrayList stopWords(String table) {
        ArrayList al = new ArrayList();
        try {
            String query = null;
            if (table.equals("stopwords")) {
                query = "select words from stopwords";
            } else {
                query = "select word from " +
                    table + " order by count desc limit 0,11";
            }
            PreparedStatement pst = conn.prepareCall(query);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                String res = rs.getString(1);
//                if (table.equals("stopwords")) {
//                    StemmerEng stem = new StemmerEng();
//                    stem.add(res.toCharArray(), res.length());
//                    stem.stem();
//                    res = stem.toString();
//                }
                al.add(res);
            }
        } catch (SQLException se) {
        }
        return al;
    }

    public ArrayList stopWords() {
        ArrayList al = new ArrayList();
        try {
            PreparedStatement pst = conn.prepareCall("select words from " +
                    "stopwords");
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                al.add(rs.getString(1));
            }
        } catch (SQLException se) {
        }
        return al;
    }

    public void closeConn() {
        try {
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DBOperator.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

//    public static void main(String[] args) {
//        DBOperator dbOp = new DBOperator();
//        String[] tmp = dbOp.getAllDoc("dic_post", "WHAT COUNTRIES HAVE NEWLY JOINED THE UNITED NATIONS");
//        for (String s : tmp) {
//            System.out.println(s);
//        }
//        System.out.println("dokumen sebanyak " + tmp.length);
//    }
}
